package com.jdbc.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.jdbc.pojo.Brand;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class BrandTest {

    /**
     * 查询所有
     * 1.select * from tb_brand;
     * 2.参数：不需要
     * 3.结果：List<Brand>
     */
    @Test
    public void testSelectAll() throws Exception {
        //1.获取Connection
        //a.加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("../jdbc_demo/src/druid.properties"));
//        prop.load(new FileInputStream("src//druid.properties"));
//        prop.load(BrandTest.class.getClassLoader().getResourceAsStream("druid.properties"));
        //b.获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        //c.获取数据库连接
        Connection conn = dataSource.getConnection();


        //2.定义sql语句
        String  sql="select * from tb_brand;";

        //3.获取pstmt对象
        PreparedStatement pstmt=conn.prepareStatement(sql);

        //4.设置参数

        //5.执行sql
        ResultSet rs= pstmt.executeQuery();

        Brand brand=null;
        List<Brand> brands=new ArrayList<>();
        //处理结果list<Brand>  封装Brand对象，装载到list集合
        while (rs.next()){
            //获取数据
            int id = rs.getInt("id");
            String brandName = rs.getString("brand_name");
            String companyName = rs.getString("company_name");
            int ordered = rs.getInt("ordered");
            String description = rs.getString("description");
            int status = rs.getInt("status");

            //封装Brand对象
            brand=new Brand();
            brand.setId(id);
            brand.setBrandName(brandName);
            brand.setCompanyName(companyName);
            brand.setOrdered(ordered);
            brand.setDescription(description);
            brand.setStatus(status);

            //装在集合
            brands.add(brand);
        }
        System.out.println(brands);
        //7.释放资源
        rs.close();
        pstmt.close();
        conn.close();




    }







    /**
     * 添加
     * 1.INSERT INTO tb_brand(brand_name,company_name,ordered,description,status) VALUES (?,?,?,?,?);
     * 2.参数：需要:除了id之外的所有参数信息
     * 3.结果：boolean
     */
    @Test
    public void testAdd() throws Exception {
        //接收页面提交的参数
        String brandName="香飘飘";
        String companyName="香飘飘";
        int ordered=1;
        String description="绕地球一圈";
        int status=1;

        //1.获取Connection
        //a.加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("../jdbc_demo/src/druid.properties"));
//        prop.load(new FileInputStream("src//druid.properties"));
//        prop.load(BrandTest.class.getClassLoader().getResourceAsStream("druid.properties"));
        //b.获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        //c.获取数据库连接
        Connection conn = dataSource.getConnection();


        //2.定义sql语句
        String  sql="INSERT INTO tb_brand(brand_name,company_name,ordered,description,status) VALUES (?,?,?,?,?)";

        //3.获取pstmt对象
        PreparedStatement pstmt=conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);

        //5.执行sql
        int count = pstmt.executeUpdate();



        //6处理结果
        System.out.println(count>0);



        //7.释放资源
        pstmt.close();
        conn.close();


    }





    /**
     * 修改
     * 1.UPDATE tb_brand
     * SET brand_name=?,
     * company_name=?,
     * ordered=?,
     * description=?,
     * status=?
     * WHERE id=?
     *
     * 2.参数：需要:所有参数信息
     * 3.结果：boolean
     */
    @Test
    public void testUpdate() throws Exception {
        //接收页面提交的参数
        String brandName="香飘飘";
        String companyName="香飘飘";
        int ordered=1000;
        String description="绕地球三圈";
        int status=1;
        int id=4;

        //1.获取Connection
        //a.加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("../jdbc_demo/src/druid.properties"));
//        prop.load(new FileInputStream("src//druid.properties"));
//        prop.load(BrandTest.class.getClassLoader().getResourceAsStream("druid.properties"));
        //b.获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        //c.获取数据库连接
        Connection conn = dataSource.getConnection();


        //2.定义sql语句
        String  sql="UPDATE tb_brand \n" +
                "SET brand_name=?,\n" +
                "company_name=?,\n" +
                "ordered=?,\n" +
                "description=?,\n" +
                "status=?\n" +
                "WHERE id=?";

        //3.获取pstmt对象
        PreparedStatement pstmt=conn.prepareStatement(sql);

        //4.设置参数
        pstmt.setString(1,brandName);
        pstmt.setString(2,companyName);
        pstmt.setInt(3,ordered);
        pstmt.setString(4,description);
        pstmt.setInt(5,status);
        pstmt.setInt(6,id);

        //5.执行sql
        int count = pstmt.executeUpdate();



        //6处理结果
        System.out.println(count>0);



        //7.释放资源
        pstmt.close();
        conn.close();


    }



    /**
     * 删除
     *DELETE FROM tb_brand WHERE id=?
     *
     * 2.参数：需要:所有参数信息
     * 3.结果：boolean
     */
    @Test
    public void testDelete() throws Exception {
        //接收页面提交的参数
        int id=4;

        //1.获取Connection
        //a.加载配置文件
        Properties prop=new Properties();
        prop.load(new FileInputStream("../jdbc_demo/src/druid.properties"));
//        prop.load(new FileInputStream("src//druid.properties"));
//        prop.load(BrandTest.class.getClassLoader().getResourceAsStream("druid.properties"));
        //b.获取连接池对象
        DataSource dataSource= DruidDataSourceFactory.createDataSource(prop);
        //c.获取数据库连接
        Connection conn = dataSource.getConnection();


        //2.定义sql语句
        String  sql="DELETE FROM tb_brand WHERE id=?";

        //3.获取pstmt对象
        PreparedStatement pstmt=conn.prepareStatement(sql);

        //4.设置参数

        pstmt.setInt(1,id);

        //5.执行sql
        int count = pstmt.executeUpdate();



        //6处理结果
        System.out.println(count>0);



        //7.释放资源
        pstmt.close();
        conn.close();


    }
}
